package com.class5.springboot.frame.repo.real.init;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import com.class5.springboot.frame.repo.exception.RepositoryException;
import com.class5.springboot.frame.repo.tool.xml.*;
import com.class5.springboot.frame.util.kengen.KeyGeneratorFactory;
import com.class5.springboot.util.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;


import lombok.extern.slf4j.Slf4j;

@Slf4j
public class DbProjectInitBean {
    public final static String INIT_TYPE_FOR_PROD = "prod";
    public final static String INIT_TYPE_FOR_TEST = "test";
    private DbAdapter da;
    private DbTracker dt;
    private JdbcTemplate jt;
    public DbProjectInitBean(JdbcTemplate jt) {
        this.jt = jt;
        this.da = new DbAdapter(jt);
        this.dt = new DbTracker(jt, da);
        dt.init();
    }
    /**
     * 更新数据库表结构
     * @param project
     * @return
     */
    public List<String> generateDatabaseUpdateSql(DbProject project, String dataUpdateType){
        log.info("【"+project.getProject()+"】开始生成数据库更新语句...");
        //需要更新的语句
        List<String> updatesqls = new ArrayList<>();
        //生成各个表的结构更新的语句
        for(DbTable table : project.getTables()) {
            generateTableDdl(project, table, updatesqls);
        }
        //初始化数据
        initData(project, dataUpdateType, updatesqls);
        log.info("【"+project.getProject()+"】数据库更新语句已生成:{}", updatesqls);
        return updatesqls;
    }

    //生成表的更新语句
    private void generateTableDdl(DbProject project, DbTable table, List<String> sqls) {
        List<String> dbtables = da.getDbTableNames();
        //考虑索引
        List<DbTableIndex> oldIndexes = null;
        if(!dbtables.contains(table.getName())) {
            //考虑表的新加
            sqls.add(dt.generateTableCreateDdl(table));
            oldIndexes = new ArrayList<>();
        }else {
            //考虑表字修改
            generateModifyDdl(project, table, sqls);
            oldIndexes = da.getIndexes(table.getName());
        }

        //考虑新加或修改索引
        for(DbTableIndex index : table.getIndexes()) {
            DbTableIndex oldIndex = null;
            for(DbTableIndex old : oldIndexes) {
                if(old.getName().equalsIgnoreCase(index.getName())) {
                    oldIndex = old;
                    break;
                }
            }
            boolean add = false;
            if(oldIndex == null) {
                add = true;
            }else if(!oldIndex.getType().equals(index.getType()) || !Arrays.toString(oldIndex.getColumns()).equals(Arrays.toString(index.getColumns()))) {
                //先删除原来的
                sqls.add("ALTER TABLE " + wrapName(table.getName()) + " DROP INDEX " + index.getName());
                add = true;
            }
            if(add) {
                String s = StringUtils.join(index.getColumns(), ",");
                sqls.add("ALTER TABLE " + wrapName(table.getName()) + " ADD INDEX " + index.getName() + "(" + s + ")");
            }
        }
        //考虑删除索引
        for(DbTableIndex old : oldIndexes) {
            boolean find = false;
            for(DbTableIndex cur : table.getIndexes()) {
                if(cur.getName().equalsIgnoreCase(old.getName())) {
                    find = true;
                    break;
                }
            }
            if(!find) {
                sqls.add("ALTER TABLE " + wrapName(table.getName()) + " DROP INDEX " + old.getName());
            }
        }
    }
    private void initData(DbProject project, String initType, List<String> sqls){
        for(DbInit init : project.getInitdatas()){
            //检查是否有初始化过
            if(!dt.isDataInited(init.getKey(), init.getVersion())){
                //需要初始化
                initData2(init, initType, sqls);
            }
        }
    }
    private void initData2(DbInit init, String initType, List<String> sqls){
        //跳过测试环境
        if(initType.equals("prod")){
            if(init.getEnv().equals("test")){
                return;
            }
        }
        for(DbInitTable table : init.getTables()){
            //跳过测试环境
            if(initType.equals("prod")){
                if(table.getEnv().equals("test")){
                    continue;
                }
            }
            for(Map<String, String> rec : table.getRecords()){
                String wstr = null;
                for(String pk : table.getPks()){
                    if(wstr == null)
                        wstr = pk + "=" + wrapData(rec.get(pk));
                    else
                        wstr = wstr  + " and " + pk + "=" + wrapData(rec.get(pk));
                    if(!rec.containsKey(pk)){
                        throw new RepositoryException("执行init[key="+init.getKey()+"]出错，Record元素必需含有所有的pks字段。");
                    }
                }
                String sqlstr = "select "+table.getPks()[0]+" from " + table.getName() + " where " + wstr;
                List<Map<String, Object>> rls = jt.queryForList(sqlstr);
                if(rls.isEmpty()){
                    //生成insert
                    if(!rec.containsKey("id")){
                        rec.put("id", KeyGeneratorFactory.generateKey()+"");
                    }
                    String colstr = "";
                    String vstr = "";
                    for(String col : rec.keySet()){
                        colstr = colstr + "`" + col + "`,";
                        vstr = vstr + wrapData(rec.get(col)) + ",";
                    }
                    colstr = colstr.substring(0, colstr.length()-1);
                    vstr = vstr.substring(0, vstr.length()-1);
                    String insert = "insert into `" + table.getName() + "`(" + colstr + ") VALUES(" + vstr + ")";
                    sqls.add(insert);
                }else{
                    //生成update
                    String setstr = "";
                    for(String col : rec.keySet()){
                        setstr = setstr + "`" + col + "`="+wrapData(rec.get(col))+",";
                    }
                    setstr = setstr.substring(0, setstr.length()-1);
                    String update = "update `" + table.getName() + "` set " + setstr + " where " + wstr;
                    sqls.add(update);
                }
            }
        }
        //更新版本
        dt.generateUpdateDataInitedVersion(init.getKey(), init.getVersion(), sqls);
    }
    private String wrapData(String val) {
        if(val == null)
            return null;
        return "'" + val.replace("'", "''") + "'";
    }
    private String wrapName(String name){
        return "`" + name + "`";
    }

    private void generateModifyDdl(DbProject project, DbTable table, List<String> sqls) {
        DbTableAdapter dta = da.getByName(table.getName());
        for(DbTableColumn col : table.getColumns()) {
            //如果没有找到,新加该列
            DbTableColumn oldCol = dta.getColumn(col.getName());
            if(oldCol == null) {
                sqls.add(dt.generateColumnCreateDdl(table, col));
            }else {
                //检查是否有修改
                if(!col.isAttrEqual(oldCol) || !col.isDefaultEqual(oldCol)) {
                    sqls.add(dt.generateColumnModifyDdl(table, col));
                }
            }
        }
        //未找到且不能为空的列，置为空
        for(DbTableColumn c : dta.getColumns()) {
            if(!c.isNullable() && !table.hasColumn(c.getName())) {
                c.setNullable(true);
                sqls.add(dt.generateColumnModifyDdl(table, c));
            }
        }
    }
    /**
     * 更新数据库结构
     * @param sqls
     */
    public void updateDatabase(List<String> sqls) {
        for(String sql : sqls) {
            jt.execute(sql);
        }
    }
    /**
     * 对比数据库生成drop语句
     * @param prj
     * @return
     */
    public List<String> getDropedDdl(DbProject prj) {
        List<String> sqls = new ArrayList<>();
        //如果有指定前缀，考虑表的删除
        if(prj.getPrefix() != null && !prj.getPrefix().isEmpty()) {
            List<DbTableAdapter> ls = da.getTables();
            for(DbTableAdapter old : ls) {
                //非当前配置项目的表跳过
                if(!old.getName().startsWith(prj.getPrefix())) {
                    continue;
                }
                if(!prj.hasTable(old.getName())) {
                    sqls.add("DROP TABLE " + wrapName(old.getName()));
                }
            }
        }
        //考虑表的列的删除
        for(DbTable table : prj.getTables()) {
            DbTableAdapter dta = da.getByName(table.getName());
            for(DbTableColumn old : dta.getColumns()) {
                if(table.getColumn(old.getName()) == null) {
                    sqls.add("ALTER TABLE " + wrapName(table.getName()) + " DROP COLUMN " + wrapName(old.getName()));
                }
            }
        }
        return sqls;
    }
}